-- EXEC SP_SORT_ALL_BIN_HISTORY_REPORT 'SORT','0','0','0','0','0','XLREPORT', '0'
ALTER PROC [dbo].[SP_SORT_ALL_BIN_HISTORY_REPORT] (
 
@PROCESS_TYPE		VARCHAR(50),
@PROCESSSTEP_IDS	VARCHAR(100),
@BASEPRODUCT_ID		VARCHAR(50),
@VENDOR			    VARCHAR(100),
@FROMDATE		    VARCHAR(50),
@TODATE 		    VARCHAR(50),
@REPORT_TYPE		VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50) 
)
 
 AS
 BEGIN	
	   	
 	-- TEMPORARTY TABLE TO DISPLAY THE RESULT OF BINS INFORMATION WITH LOTNUMBER AND PARTNUMBER FOR THE WAFERS
 	CREATE TABLE #RESULT_TABLE( [PART NUMBER] VARCHAR(50),VENDOR VARCHAR(50), [Subcon Lot No] varchar(50),[Wafer Lot No] VARCHAR(50),[Customer Lot No] VARCHAR(50), [WAFER ID] INT,[Test Time(min)]VARCHAR(50),
				[Lot Type] VARCHAR(10),Temperature VARCHAR(10),Insertion VARCHAR(20), [Program No] VARCHAR(100), [Tester ID] VARCHAR(20),[Prober ID] VARCHAR(20),	[Load Board] VARCHAR(50),
				[Probe Card] VARCHAR(50),[In Date] DATETIME, [Out Date] DATETIME,GDPW INT,[Good Dies] INT, Yield FLOAT,
 				[Bin 1] INT, [Bin 2] INT, [Bin 3] INT, [Bin 4] INT, [Bin 5] INT, [Bin 6] INT, [Bin 7] INT, [Bin 8] INT,	[Bin 9] INT, [Bin 10] INT,
				[Bin 11] INT,[Bin 12] INT,[Bin 13] INT,[Bin 14] INT,[Bin 15] INT,[Bin 16] INT,[Bin 17] INT,[Bin 18] INT,[Bin 19] INT,[Bin 20] INT,
				[Bin 21] INT,[Bin 22] INT,[Bin 23] INT,[Bin 24] INT,[Bin 25] INT,[Bin 26] INT,[Bin 27] INT,[Bin 28] INT,[Bin 29] INT,[Bin 30] INT,
				[Bin 31] INT,[Bin 32] INT,[Bin 33] INT,[Bin 34] INT,[Bin 35] INT,[Bin 36] INT,[Bin 37] INT,[Bin 38] INT,[Bin 39] INT,[Bin 40] INT,
				[Bin 41] INT,[Bin 42] INT,[Bin 43] INT,[Bin 44] INT,[Bin 45] INT,[Bin 46] INT,[Bin 47] INT,[Bin 48] INT,[Bin 49] INT,[Bin 50] INT,
				[Bin 51] INT,[Bin 52] INT,[Bin 53] INT,[Bin 54] INT,[Bin 55] INT,[Bin 56] INT,[Bin 57] INT,[Bin 58] INT,[Bin 59] INT,[Bin 60] INT,
				[Bin 61] INT,[Bin 62] INT,[Bin 63] INT,[Bin 64] INT,[Bin 65] INT,[Bin 66] INT,[Bin 67] INT,[Bin 68] INT,[Bin 69] INT,[Bin 70] INT,
				[Bin 71] INT,[Bin 72] INT,[Bin 73] INT,[Bin 74] INT,[Bin 75] INT,[Bin 76] INT,[Bin 77] INT,[Bin 78] INT,[Bin 79] INT,[Bin 80] INT,
				[Bin 81] INT,[Bin 82] INT,[Bin 83] INT,[Bin 84] INT,[Bin 85] INT,[Bin 86] INT,[Bin 87] INT,[Bin 88] INT,[Bin 89] INT,[Bin 90] INT,
				[Bin 91] INT,[Bin 92] INT,[Bin 93] INT,[Bin 94] INT,[Bin 95] INT,[Bin 96] INT,[Bin 97] INT,[Bin 98] INT,[Bin 99] INT,[Bin 100] INT )
 	
 	DECLARE @SQLSTRING NVARCHAR(4000)
 	 
 	SET @SQLSTRING = N' INSERT INTO #RESULT_TABLE SELECT DISTINCT PART_NUMBER, L_VENDOR_NAME, VENDOR_LOT, LOT_NUMBER,LOT_NUMBER, WAFER_ID,TEST_TIME,Lot_Category, Temp, Insertion,
		SL_Test_Program, SL_Tester, Probe_Id, '''', Probe_card, SBI.DATE_IN,SBI.DATE_OUT,Gross_Die,Good_Dice,YIELD,
		 Bin_1 ,  Bin_2 ,  Bin_3 ,  Bin_4 ,  Bin_5 ,  Bin_6 ,  Bin_7 ,  Bin_8 ,	 Bin_9 ,  Bin_10 ,
		 Bin_11 , Bin_12 , Bin_13 , Bin_14 , Bin_15 , Bin_16 , Bin_17 , Bin_18 , Bin_19 , Bin_20 ,
		 Bin_21 , Bin_22 , Bin_23 , Bin_24 , Bin_25 , Bin_26 , Bin_27 , Bin_28 , Bin_29 , Bin_30 ,
		 Bin_31 , Bin_32 , Bin_33 , Bin_34 , Bin_35 , Bin_36 , Bin_37 , Bin_38 , Bin_39 , Bin_40 ,
		 Bin_41 , Bin_42 , Bin_43 , Bin_44 , Bin_45 , Bin_46 , Bin_47 , Bin_48 , Bin_49 , Bin_50 ,
		 Bin_51 , Bin_52 , Bin_53 , Bin_54 , Bin_55 , Bin_56 , Bin_57 , Bin_58 , Bin_59 , Bin_60 ,
		 Bin_61 , Bin_62 , Bin_63 , Bin_64 , Bin_65 , Bin_66 , Bin_67 , Bin_68 , Bin_69 , Bin_70 ,
		 Bin_71 , Bin_72 , Bin_73 , Bin_74 , Bin_75 , Bin_76 , Bin_77 , Bin_78 , Bin_79 , Bin_80 ,
		 Bin_81 , Bin_82 , Bin_83 , Bin_84 , Bin_85 , Bin_86 , Bin_87 , Bin_88 , Bin_89 , Bin_90 ,
		 Bin_91 , Bin_92 , Bin_93 , Bin_94 , Bin_95 , Bin_96 , Bin_97 , Bin_98 , Bin_99 , Bin_100
								FROM PRODUCT P, LOT_TABLE LT, LOCATION L,SORT_LOT SL, SORT_BINNING_INFO SBI
							WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.LOCATION_ID=L.LOCATION_ID AND LT.LOT_ID = SL.LOT_ID AND SL.LOT_ID = SBI.LOT_ID
								 AND LOT_CURRENT_STATUS <> ''Running'' '

		IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = ''' + @PROCESSSTEP_IDS + '''' 
		END 

		IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID =  ''' + @BASEPRODUCT_ID + '''' 
		END
		
		IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' and L.VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') )'
		END

		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SBI.DATE_OUT >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		END

		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SBI.DATE_OUT <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		END
		
		SET @SQLSTRING = @SQLSTRING + ' UNION
		SELECT DISTINCT PART_NUMBER, (SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID= LT.LOCATION_ID),'''',LOT_NUMBER,LOT_NUMBER, WAFER_ID,'''',Lot_Category, '''', '''',
		SL_Test_Program, SL_Tester, '''', '''', '''', LT.DATE_IN,LT.DATE_OUT,
		( convert(int,CEILING(cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Gross Die/Wafer'' 
					AND PROCESSSTEP_ID = (SELECT Top 1 PROCESSSTEP_ID FROM PROCESS_STEP_TRAVERSE_TABLE WHERE NEXT_PROCESSSTEP_ID = (SELECT Top 1 PROCESSSTEP_ID FROM PROCESS_STEP_TRAVERSE_TABLE WHERE NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID )) ) as float) ) )
		 ),
		 Good_Dice,YIELD,
		 Bin_1 ,  Bin_2 ,  Bin_3 ,  Bin_4 ,  Bin_5 ,  Bin_6 ,  Bin_7 ,  Bin_8 ,	 Bin_9 ,  Bin_10 ,
		 Bin_11 , Bin_12 , Bin_13 , Bin_14 , Bin_15 , 0 , 0 , 0 ,0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ,
		 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 
			FROM PRODUCT P, LOT_TABLE LT, SORT_LOT SL, Informative_Binning IB
			WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.LOT_ID = SL.LOT_ID AND SL.LOT_ID = IB.LOT_ID 
			AND IB.LOT_ID NOT IN ( SELECT DISTINCT LOT_ID FROM SORT_BINNING_INFO ) AND LOT_CURRENT_STATUS=''CLOSE'' '
 	
	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = ''' + @PROCESSSTEP_IDS + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID =  ''' + @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' and LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_TYPE = ''SORT'' AND VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') ) )' 
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.DATE_OUT >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.DATE_OUT <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
 
 	--SET @SQLSTRING = @SQLSTRING + ' ORDER BY PART_NUMBER,DATE_IN,DATE_OUT '
 
 	--PRINT @SQLSTRING
 	EXECUTE SP_EXECUTESQL @SQLSTRING
 
 	SELECT * FROM #RESULT_TABLE ORDER BY [PART NUMBER],[Wafer Lot No],[WAFER ID]

END	